#!/usr/bin/env python
# encoding: utf-8
#################
# Name:         一点接口用户数据，清洗 
# Purpose:
#
# Author:      stephen
#
# Created:     02/20/2019
# Licence:     1.0.0
#
#################
import logging
import ConfigParser
import re, os, random, sys, string, hashlib, math, uuid
import calendar
import json,struct
import numpy as np
import pymysql

#from datetime import *
import datetime
import time
from LOCAL_CONFIG import *
from phone import Phone
from birth import Birth

reload(sys)
sys.setdefaultencoding('utf-8')
#sys.path.append(os.environ['HOME']+"/bin")
sys.path.append(os.environ['HOME']+"/lib")  
from pyctlib import *

import re


VERSION_NUM  = '1.0.0'
VERSION_DATE = '2019-02-20'

class UniDB:
  def __init__(self,user,passwd,server,port,dbname,dbtype="Oracle"):
    self.__server = server
    self.__port   = port
    self.__dbname = dbname
    self.__user   = user
    self.__passwd = passwd
    self.__dbtype = dbtype.lower()
    self.dbconn   = None
  def __del__(self):
    pass
  def open(self):
    if self.__dbtype == "oracle":
      os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import cx_Oracle
      dsn_tns = cx_Oracle.makedsn(self.__server, self.__port  , self.__dbname)
      self.dbconn=cx_Oracle.connect(self.__user, self.__passwd, dsn_tns)
    elif self.__dbtype == "mysql":
      #os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.UTF8'
      import pymysql
      self.dbconn=pymysql.connect(host  =self.__server, port=self.__port, user=self.__user,\
                                  passwd=self.__passwd, db  =self.__dbname, charset='utf8')
    else:
      raise TypeError("OnlyOracle.")
    logging.info("DB[%s] Open."%self.__dbtype)
  def close(self):
    try:
      if self.dbconn:
        self.dbconn.close()
      self.dbconn=None
      logging.info("DB[%s]Closed."%self.__dbtype)
    except Exception,e:
      logging.info("DB[%s]ClosedExcept[%s]."%(self.__dbtype,e))
      self.dbconn=None
      
class UniCfg:
  def __init__(self,cfgfile):
    self.cfgfile=cfgfile
    self.load_cfg()
  def load_cfg(self):
    cf = ConfigParser.ConfigParser()
    cf.read(self.cfgfile)
    
    self.dbhost = cf.get("db"   , "dbhost")
    self.dbport = cf.getint("db", "dbport")
    self.dbname = cf.get("db"   , "dbname")
    self.dbuser = cf.get("db"   , "dbuser")
    self.dbpass = cf.get("db"   , "dbpass")
    self.dbtype = cf.get("db"   , "dbtype")
    
    self.logfile = cf.get("log","logfile")
    self.loglevel= cf.get("log","loglevel").upper()

class Handle:
  def __init__(self,udb=None,cfg=None):
    self.dbconn  = udb.dbconn
    self.cfg  = cfg
    self.__user_id     = 1
    self.__table       = 'usr_user_basic_info'
    self.__ph = Phone()

 
  def handle_data (self):
    try:
      self.__handle_client_user()
    except  Exception,err:                                                   
      import traceback                                                       
      import StringIO                                                        
      fp = StringIO.StringIO()                                               
      traceback.print_exc(file=fp)                                           
      message = fp.getvalue()                                                
      logging.info(message)                                                  
                                                                             
      logging.warning('err:%s %s' %(err, sys._getframe().f_back.f_code) ) 

  def __handle_client_user(self ):
    sql = "select * from yd_client_user "
    #sql = "select * from yd_client_user where inner_id = 14011 "
    res = self.__select_db(sql, True) 
 
    index = 0 
    cursor = self.dbconn.cursor()
    for re in res:
      if not re.get('mobile'):
        continue
      if  not self.is_valid_mobile(re.get('mobile')):
        continue 
      query= self.__handle_user_info( re )
      if query: 
        logging.info(query)
        cursor.execute(query)
        if index%500 == 0:                                                   
          logging.info('index %d db commit'%(index))                         
          self.dbconn.commit() 
        index += 1
    logging.info('end index %d db commit'%(index))                           
    self.dbconn.commit() 
    cursor.close()

  def __escape_dict(self, data_dict):
    if type(data_dict) != dict or len(data_dict)<1:                          
      return a
    for key, val in data_dict.items():     
      if val is None:
        continue
      if  type(val)==str or type(val) == unicode:
        data_dict[key] =  re.escape(val)
   
  def __handle_user_info(self,data_dict ):
     
    if type(data_dict) != dict or len(data_dict)<1:
      return

    self.__escape_dict( data_dict) 
 
    add_data = {} 
    #ph = Phone()
    p_info = self.__ph.find(data_dict['mobile'])
    if not p_info:
      return
    if data_dict.get('mobile') and  self.is_valid_mobile( data_dict.get('mobile') ):
      add_data['mobile'] = data_dict.get('mobile')
      add_data['mobile_prefix3'] = str(data_dict.get('mobile'))[:3] 
      add_data['mobile_prefix7'] = str(data_dict.get('mobile'))[:7]
      add_data['mobile_isp']     = p_info['phone_type']
      add_data['mobile_belong_province'] = p_info['province']
      add_data['mobile_belong_city']     = p_info['city'] 


    if data_dict.get('nick_name'):
      add_data['nick_name'] = data_dict.get('nick_name')

    if data_dict.get('head_img'):
      add_data['head_img']  = data_dict.get('head_img')

    if  self.__get_user_sex(data_dict):
      add_data['user_sex'] = self.__get_user_sex(data_dict)  
  
    if data_dict.get('register_time'):
      add_data['register_time']  = data_dict.get('register_time')
    if data_dict.get('birthday'):
      birth = Birth()                                                          
      if birth.get_age(data_dict.get('birthday')):
        add_data['birthday']  = data_dict.get('birthday')
        add_data['cur_age']   = birth.get_age(data_dict.get('birthday')) 
        add_data['constellation'] = birth.get_constellation(data_dict.get('birthday'))
    if data_dict.get('user_credits') and data_dict.get('user_credits').isdigit() :
      add_data['user_credits']  = data_dict.get('user_credits')
    if data_dict.get('company'):
      add_data['company'] = data_dict.get('company')
    if data_dict.get('reg_imei'):
      add_data['imei'] = data_dict.get('reg_imei')

    if len(add_data) <1:
      return
  
    cnt = self._table_cnt_count('usr_user_basic_info', "mobile='%s'"%(\
                      data_dict.get('mobile')))
     
    if cnt == 0:
      add_sql = self.generate_insert_sql(add_data)
      #logging.info(add_sql)
      if add_sql is not None:
        return add_sql
        #self.__update_db(add_sql)
        #self.__handle_datasource_rel(add_data, 1, data_dict['inner_id'] )
      
    else:
      update_sql = self.generate_update_sql(add_data)
      #logging.info(update_sql)
      if  update_sql is not None:
        return update_sql
        #self.__update_db(update_sql)
        #self.__handle_datasource_rel(add_data, 2,data_dict['inner_id'] )


  def __get_user_sex(self,  data_dict ):
    if type(data_dict) != dict or len(data_dict)<1:
      return
  
    if not data_dict.has_key('sex'):
      return   
    if data_dict.get('sex'):
      if str(data_dict.get('sex')).isdigit():
        if int(data_dict.get('sex')) ==1:
          return '男'
        elif int(data_dict.get('sex')) ==2:
          return '女'
        else:
          return '未知'
      else: 
        sex = str(data_dict.get('sex'))
        if sex == '男':
          return '男'
        elif sex == '女':
          return  '女'
        else:
          return  '未知'
    else:
      return  '未知'
   
  def __handle_birthday(self, add_dict):
    if type(add_dict)!= dict or len(add_dict)<1:
      return
    if len(str(add_dict.get('birthday'))) != 8 or not \
       str(add_dict.get('birthday')).isdigit():
      logging.error('birthday:%s. error'%(add_dict['birthday']) )
      return
    birth = Birth()
    if birth.get_age(add_dict.get('birthday') ) is None:
      logging.error('birthday:%s. error'%(add_dict['birthday']))
      return 

    wh = ''                                                                  
    if add_dict.has_key('mobile'):                                           
      wh += "where mobile = '%s'"%(add_dict['mobile'])                       
    else:                                                                    
      if add_dict.has_key('imei'):                                           
        wh += "where imei= '%s'"%(add_dict['imei'])                          
    if len(wh) <1:                                                           
      return   
    sql = "select cur_age, constellation from usr_user_basic_info %s"%(wh)
    res = self.__select_db(sql, True)
    for row in res:                                                          
      if not row['cur_age'] or not row['constellation'] or not \
        str(row['cur_age']).isdigit() or int(row['cur_age']) != \
        birth.get_age( add_dict['birthday'] )  or \
        str(row['constellation']) !=  birth.get_constellation( add_dict['birthday'] ):
        up_sql = "update usr_user_basic_info set cur_age= %s, \
                  constellation= '%s', update_user_id = %d, update_time='%s' \
                  %s" %(birth.get_age( add_dict['birthday'] ),\
                    birth.get_constellation(add_dict['birthday']), 1, \
                 datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), wh) 

        #logging.info(up_sql)                                                
        self.__update_db(up_sql)                                             
                                  

   

  def __handle_mobile_prefix(self, add_dict):
    if type(add_dict)!= dict or len(add_dict)<1:
      return
    if  len(str(add_dict.get('mobile'))) != 11:
      return
    sql = "select mobile_prefix3, mobile_prefix7 from usr_user_basic_info \
           where mobile='%s'"%(add_dict['mobile'])
      
    res = self.__select_db(sql, True)
    for row in res:
      if not row['mobile_prefix3'] or len(str(row['mobile_prefix3'])) != 3 or not \
         str(str(row['mobile_prefix3'])).isdigit():
        sql_3 = "update usr_user_basic_info set mobile_prefix3= '%s', \
                update_user_id = %d, update_time='%s'  where mobile='%s'"%( \
              add_dict['mobile'][:3],1, \
             datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), add_dict['mobile'] )   
        #logging.info(sql_3)
        self.__update_db(sql_3)
      if not row['mobile_prefix7'] or len(str(row['mobile_prefix7'])) != 7 or not \
        str(str(row['mobile_prefix7'])).isdigit():
        sql_7 = "update usr_user_basic_info set mobile_prefix7= '%s', \
                update_user_id = %d, update_time='%s'  where mobile='%s'"%( \
              add_dict['mobile'][:7],1, \
             datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), add_dict['mobile'] )
        #logging.info(sql_7)
        self.__update_db(sql_7) 
  
  #update mobile informattion 
  def __handle_mobile_area_provice(self, add_dict):
    if type(add_dict)!= dict or len(add_dict)<1:
      return
    if  not add_dict.has_key('mobile') and len(str(add_dict.get('moible'))) != 11:
      return
    #ph = Phone()
    p_info = self.__ph.find(add_dict['mobile'])
    if not p_info:
      #logging.info('mobile:%s, error, not found' %(add_dict['mobile']) )
      return
    up_sql = "update usr_user_basic_info set mobile_isp='%s', \
               mobile_belong_province = '%s', mobile_belong_city='%s', \
               update_user_id = %d, update_time='%s' \
               where mobile='%s' " %(p_info['phone_type'],\
               p_info['province'], p_info['city'], 1, \
               datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"), 
               add_dict['mobile'] ) 
    se_sql = "select mobile_isp, mobile_belong_province, mobile_belong_city \
           from usr_user_basic_info where mobile='%s'"%(add_dict['mobile']) 
    res = self.__select_db(se_sql, True)
    for row in res:
      if not row['mobile_isp'] or not row['mobile_belong_province'] or not \
        row['mobile_belong_city']  or \
        str(row['mobile_isp']) != p_info['phone_type'] or  \
        str(row['mobile_belong_province']) != p_info['province'] or \
        str(row['mobile_belong_city']) != p_info['city'] :  
        #logging.info(up_sql)
        self.__update_db(up_sql)
      

       
         
 
  # insert data to datasource_rel  
  def __handle_datasource_rel(self, add_dict, opt_type, inner_id ):
    if type(add_dict) != dict or len(add_dict) <1:
      return
    wh = ''
    if add_dict.get('mobile'):
      wh += "where mobile = '%s'"%(add_dict['mobile'])
    if len(wh) <1:
      return  
 
    sql = "select user_id from usr_user_basic_info %s"%(wh)
    res = self.__select_db(sql, True)
    if len(res)>0 and res[0].has_key('user_id'): 
      if res[0]['user_id'] is not None:
        user_id = res[0]['user_id']

    if  user_id>0:
      sql=  "insert into usr_user_datasource_rel (user_id, user_business_id, \
            templet_id,import_log_id, opt_type, putin_ware_time ) values \
            (%s, %s,1,1, %d,'%s') "%( user_id, inner_id, opt_type, \
		        datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
      #logging.info(sql)
      self.__update_db(sql)

  #generate insert sql
  def generate_insert_sql(self, add_dict):
    if type(add_dict) != dict or len(add_dict) <1:
      return

    insert_sql = "insert into usr_user_basic_info ( "
    values_sql = "values ("
    for key, val in add_dict.items():
      insert_sql += key + ','
      if type(val) == int:
        values_sql += '%d'%(val) + ','
      elif type(val) == float:
        values_sql += '%f'%(val) + ','                                       
      else:                                                                  
        values_sql += "'%s'"%(val) +','                                      
    insert_sql += "add_user_id, add_time" +','                               
    values_sql += "%d, '%s' "%(1, datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) +','
    if insert_sql[-1] == ',':                                                
      insert_sql = insert_sql[:-1] + ') '                                    
    if values_sql[-1]==',':                                                  
      values_sql = values_sql[:-1] + ') '  

    sql = insert_sql + values_sql                                            
                                                                             
    return sql  
  

  #generate sql                                                              
  def generate_update_sql(self, add_dict):                                   
    if type(add_dict) != dict or len(add_dict) <1:                           
      return                                                                 
                                                                             
    wh = ''                                                                  
    update_key = ''                                                          
    if add_dict.has_key('mobile'):                                           
      wh += "where mobile = '%s'"%(add_dict['mobile'])                       
      update_key += 'mobile'                                                 
    if len(wh) <1:                                                           
      return                                                                 
    if (update_key)<1:                                                       
      return 

    update_sql = "update usr_user_basic_info  set " 
    for key, val in add_dict.items():
       
      if key == update_key:
        continue
      if type(val) == int:
        update_sql +=  '%s = %d'%(key, val) + ','
      elif type(val) == float:
        update_sql += ' %s = %f'%(key, val) + ','                            
      else:                                                                  
        update_sql += "%s = '%s' "%(key, val) +','                            
    update_sql += " update_user_id = %d, update_time='%s' "%(1, \
                 datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")) +',' 
    if update_sql[-1]==',':                                                  
      update_sql = update_sql[:-1]                                           
    update_sql += wh 
                                                                             
    return update_sql
 
	  

  #mobile valid, return true or None    
  def is_valid_mobile(self, mobile):
    if mobile is None:
      return False
    mobile = str(mobile).strip()
    if  mobile.find('.') != -1:
      mobile = mobile[ :mobile.find('.')]
    if len(mobile) != 11 or not mobile.isdigit():
      return False
    #ph = Phone()
    if not self.__ph.find(mobile):
      return False
    else:
      return True


  #check imei is valid. 15 number
  def is_valid_imei(self, imei):
    if imei is None:
      return False
    imei = str(imei).strip()
    if imei.find('.') != -1:
      imei = imei[:imei.find('.')]
    if len(imei) == 15 and  imei.isdigit():
      return True

    return False


  # in dict all bytearray convert to unicode, check other  
  def  convert_data(self,  data=None):
    if data is None:
      raise TypeError( 'data error. %s'%(sys._getframe().f_back.f_code) ) 
    if type(data) == dict:
      # data format 
      for key, val  in data.items():
        #print('key:%s type:%s'%(key, type(val) ))
        if type(val) == bytearray:
          val = val.decode(encoding='utf8')
        if type(val) == unicode or type(val)==str:
          val = val.strip()
          if key == 'cur_age' or key=='mobile' or key=='birthday':
            if val.find('.') != -1:
              data[key] = val[:val.find('.') ]

      #data type
      for  key, val in data.items():
        if val is None:
          data.pop(key)
        if key  == 'birthday':
          if val is None or len(str(val) ) != 8 or not str(val).isdigit():
            data.pop(key)
            continue
          birth = Birth()
          if birth.get_age(val) is None:
            data.pop(key)
            continue


	  
	
  #通过select count 查询数据
  def _table_cnt_count(self,  table=None, wh=None ):
    if table == None:
      raise TypeError('tale name is errro')
    sql = "select count(1) from %s "%(table)
    if wh:
      sql +=  'where %s'%( wh)                                               
    
    res = self.__select_db(sql)                                              
    #print( res )                                                            
    return res[0][0]  

  

  def __update_db(self, sql):
    try:
      cursor = self.dbconn.cursor()
      cursor.execute(sql)
      #logging.info(sql)
      self.dbconn.commit()
      cursor.close()
    except Exception,err:
      logging.warning('err:%s' %(err) )

  def __select_db(self, sql, dictionary=None):
    try:
      if dictionary:
        cursor = self.dbconn.cursor( pymysql.cursors.DictCursor )
      else:
        cursor = self.dbconn.cursor()                                        
     
      self.dbconn.commit()
      cursor.execute(sql )                                                    
      res = cursor.fetchall()                                                
      cursor.close()                                                         
      return res                                                             
    except Exception,err:                                                    
      logging.warning('err:%s' %(err) )  




   

def main():
   if len(sys.argv)<>3 or sys.argv[1]<>"-f":
     print "Run Like: %s -f %s.conf"%(sys.argv[0][:-3],sys.argv[0][:-3])
     exit(1)
   cfg_file = None
   if sys.argv[2].find('/')>=0:
     cfg_file=sys.argv[2].find('/')
   else:
     cfg_file=os.environ['HOME']+"/cfg/"+sys.argv[2]
   cfg = UniCfg(cfg_file)
   
   if cfg.loglevel == "DEBUG":
     iLogLevel = logging.DEBUG
   elif cfg.loglevel == "INFO":
     iLogLevel = logging.INFO
   elif cfg.loglevel == "WARN" or cfg.loglevel == "WARNING":
     iLogLevel = logging.WARNING
   else:
     iLogLevel = logging.ERROR
   logging.basicConfig( filename=cfg.logfile,
                        format  = "%(asctime)s %(levelname)s[%(process)d]:%(message)s",
                        level   = iLogLevel)
   
   logging.info("......START.........")
   UDB = UniDB(cfg.dbuser,cfg.dbpass,cfg.dbhost,cfg.dbport,cfg.dbname,cfg.dbtype)
   UDB.open()
   
   obj = Handle(UDB,cfg)
   obj.handle_data()
   
   UDB.close()
   logging.info("......EXIT.........")
   logging.shutdown()
   return

if __name__ == "__main__":
  main()
